# 将多个内容一致的Excel文件合并到一个Excel文件中
#
# 小宝剑大药房（高新店）2018年销售数据.xlsx
# 小宝剑大药房（犀浦店）2018年销售数据.xlsx
# 小宝剑大药房（新津店）2018年销售数据.xlsx
from copy import copy
import openpyxl


def get_data(sheet):
    all_data = sheet[sheet.dimensions]
    # print(all_data)
    # print('*' * 100)
    rows = all_data[2:]
    # print(rows)
    sheet_data = []
    for row in rows:
        sub_data = []
        for data in row:
            if data.value is not None:
                # print(data.value)
                sub_data.append(data.value)
        if sub_data:
            sheet_data.append(sub_data)
    return sheet_data


def unify_formatting(sheet, model_row=1):
    """
    :param sheet: 工作表对象
    :param model_row: 模板行
    :return: None
    AI生成
    """
    # 获取标题行各列的样式
    header_styles = []
    for cell in sheet[model_row]:
        header_styles.append({
            'font': copy(cell.font),  # 使用copy函数
            'fill': copy(cell.fill),
            'border': copy(cell.border),
            'alignment': copy(cell.alignment),
            'number_format': cell.number_format
        })

    # 应用样式到所有数据行
    for row in sheet.iter_rows(min_row=model_row + 1, max_row=sheet.max_row):
        for idx, cell in enumerate(row):
            if idx < len(header_styles):
                style = header_styles[idx]
                cell.font = style['font']
                cell.fill = style['fill']
                cell.border = style['border']
                cell.alignment = style['alignment']
                cell.number_format = style['number_format']


if __name__ == '__main__':

    # 1. 将第一个工作簿复制
    wb0 = openpyxl.open(r'Day27\小宝剑大药房（新津店）2018年销售数据.xlsx')
    wb0.save(r'Day27\小宝剑大药房（merge）2018年销售数据.xlsx')

    # 2. 获取另外两张表的数据
    # get_data_from_wb() -> list
    wb1 = openpyxl.open(r'Day27\小宝剑大药房（犀浦店）2018年销售数据.xlsx')
    wb2 = openpyxl.open(r'Day27\小宝剑大药房（高新店）2018年销售数据.xlsx')

    # print(wb0.sheetnames, wb1.sheetnames, wb2.sheetnames)
    sheet1 = wb1['工作表 1']
    sheet2 = wb2['工作表 1']
    # print(sheet0.dimensions)
    sheet1_data = get_data(sheet1)
    print(sheet1_data)
    print('*'*100)
    sheet2_data = get_data(sheet2)
    print(sheet2_data)
    print('*'*100)
    merge_data = sheet1_data + sheet2_data
    print(merge_data)

    # 3. 将数据传入
    # write_data()
    wb = openpyxl.open(r'Day27\小宝剑大药房（merge）2018年销售数据.xlsx')
    sheet = wb['工作表 1']
    for row in merge_data:
        sheet.append(row)

    # 4. 删除空行 —— AI =================================================================================================
    # delete_empty_row()
    for row in reversed(range(1, sheet.max_row + 1)):
        # 检查整行是否为空
        is_empty = True
        for cell in sheet[row]:
            if cell.value is not None:
                is_empty = False
                break

        # 如果是空行则删除
        if is_empty:
            sheet.delete_rows(row)

    # 5. 统一格式 —— AI =================================================================================================
    unify_formatting(sheet, model_row=3)

    # 6. 保存
    wb.save(r'Day27\小宝剑大药房（merge）2018年销售数据.xlsx')